package itsm.isperp.framework.data.mybatis.dao;

import itsm.isperp.framework.collection.tree.ITreeNode;
import itsm.isperp.framework.data.dialect.DialectFactory;
import itsm.isperp.framework.data.mybatis.domain.AbstractTreeNode;
import itsm.isperp.framework.data.mybatis.utils.CUDMapperObjectUtils;

import java.util.Map;

import javax.persistence.Table;

import org.apache.ibatis.jdbc.SQL;
import org.springframework.data.jdbc.support.DatabaseType;

/**
 * 树形结构数据的数据操作模板
 * 
 * @author lizx
 * @date 2013-2-15
 * @date 2013-8-7 使用SQL替换SqlBuilder
 * @since 1.0
 */
public class TreeNodeTemplate<T extends AbstractTreeNode> extends
		CUDTemplate<T> {

	/**
	 * 更新当前即诶但的更新路径的sql语句（利用/需要加入dialect才可支持多数据库原生的sql）
	 * 
	 * @param obj
	 *            实体对象
	 * @return
	 */
	public String updatePath(T obj) {

		Table table = obj.getClass().getAnnotation(Table.class);
		if (table == null)
			return null;

		SQL sql = new SQL();
		String idname = CUDMapperObjectUtils.getPrimaryKeyName(obj.getClass());

		// 在插入时同时需要更新节点路径

		if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) {
			sql.UPDATE(table.name() + " a INNER JOIN (select " + idname
					+ ",treenode_path,treenode_name_path from " + table.name()
					+ " b) c on a.parent_id=c.id ");
			sql.SET("a.treenode_path=CONCAT(c.treenode_path,a.id,'"
					+ ITreeNode.DEFAULT_PATH_SEPARATOR
					+ "'),a.treenode_name_path=CONCAT(c.treenode_name_path,a.name,'"
					+ ITreeNode.DEFAULT_PATH_SEPARATOR + "')");
		} else {
			sql.UPDATE(table.name() + " a ");

			sql.SET("(a.treenode_path, a.treenode_name_path)=(SELECT  CONCAT(CONCAT(b.treenode_path, a.id), '/'), CONCAT( CONCAT(b.treenode_name_path, a.name), '/') FROM "
					+ table.name() + " b  WHERE a.parent_id = b.id )");
		}

		sql.WHERE("a." + idname + "='" + obj.getId()
				+ "' and a.parent_id is not null");

		return sql.toString();
	}

	/**
	 * 跟新树节点下所有子节点的路径（使用replace函数）
	 * 
	 * @param parameters
	 *            数据实体及新的路径（newTreenodePath）及（newTreenodeNamePath）
	 * @return sql跟新语句
	 */
	public String updateAllPath(Map<String, Object> parameters) {

		@SuppressWarnings("unchecked")
		T obj = (T) parameters.get("entity");
		String newTreenodePath = (String) parameters.get("newTreenodePath");
		String newTreenodeNamePath = (String) parameters
				.get("newTreenodeNamePath");
		Class<? extends AbstractTreeNode> clazz = obj.getClass();

		Table table = clazz.getAnnotation(Table.class);
		if (table == null)
			return null;
		SQL sql = new SQL();
		// String idname = CUDMapperObjectUtils.getPrimaryKeyName(clazz);

		// 在插入时同时需要更新节点路径
		sql.UPDATE(table.name());
		sql.SET("treenode_path=replace(treenode_path,'" + obj.getTreenodePath()
				+ "','" + newTreenodePath
				+ "'),treenode_name_path=replace(treenode_name_path,'"
				+ obj.getTreenodeNamePath() + "','" + newTreenodeNamePath
				+ "')");

		// WHERE(idname + "='" + obj.getId() + "'");

		return sql.toString();

	}

	/**
	 * 
	 * @param treeNodePath
	 * @param treenodeNamePath
	 * @param obj
	 * @param id
	 * @return
	 */
	public String updateTreenodePath(Map<String, Object> parameters) {
		T obj = (T) parameters.get("entity");
		String treeNodePath = (String) parameters.get("treeNodePath");
		String treenodeNamePath = (String) parameters.get("treenodeNamePath");
		Class<? extends AbstractTreeNode> clazz = obj.getClass();
		Table table = clazz.getAnnotation(Table.class);
		if (table == null)
			return null;
		SQL sql = new SQL();
		String idname = CUDMapperObjectUtils.getPrimaryKeyName(clazz);

		// 在插入时同时需要更新节点路径
		sql.UPDATE(table.name());
		sql.SET("treenode_path='" + treeNodePath + "',treenode_name_path='"
				+ treenodeNamePath + "'");

		sql.WHERE(idname + "='" + obj.getId() + "'");

		return sql.toString();

	}

	/**
	 * 
	 * @param treeNodePath
	 * @param treenodeNamePath
	 * @param obj
	 * @param id
	 * @return
	 */
	public String updateAllTreenodePath(String newTreeNodePath,
			String oldTreeNodePath, String newTreenodeNamePath,
			String oldTreenodeNamePath, T obj, String id) {

		Class<? extends AbstractTreeNode> clazz = obj.getClass();
		Table table = clazz.getAnnotation(Table.class);

		if (table == null)
			return null;
		SQL sql = new SQL();
		// String idname = CUDMapperObjectUtils.getPrimaryKeyName(clazz);

		// 在插入时同时需要更新节点路径
		sql.UPDATE(table.name());
		sql.SET("treenode_path=replace(treenode_path,'" + oldTreeNodePath
				+ "','" + newTreeNodePath
				+ "'),treenode_name_path=replace(treenode_name_path,'"
				+ oldTreenodeNamePath + "'," + newTreenodeNamePath + "')");

		// WHERE(idname + "='" + id + "'");

		return sql.toString();

	}

	public String deleteByTreenodePath(T obj) {

		Class<? extends AbstractTreeNode> clazz = obj.getClass();
		Table table = clazz.getAnnotation(Table.class);

		if (table == null)
			return null;
		SQL sql = new SQL();
		sql.DELETE_FROM(table.name());
		sql.WHERE("treenode_path Like '" + obj.getTreenodePath() + "%'");
		return sql.toString();
	}

	/**
	 * 查询指定父节点指定名称的子节点数量
	 * 
	 * @param parameters
	 *            name,parentId及clazz
	 * @return 统计节点数目的sql语句
	 */
	public String countByParentId(Map<String, Object> parameters) {
		String name = (String) parameters.get("name");
		String parentId = (String) parameters.get("parentId");
		Class<?> clazz = (Class<?>) parameters.get("clazz");

		Table table = clazz.getAnnotation(Table.class);

		if (table == null)
			return null;

		SQL sql = new SQL();

		sql.SELECT(" count(*) ");
		sql.FROM(table.name());
		sql.WHERE(" name='" + name + "' AND parent_id='" + parentId + "'");
		return sql.toString();
	}

	/**
	 * 查询指定父节点指定名称的子节点数量
	 * 
	 * @param parameters
	 *            name,parentId及clazz
	 * @return 统计节点数目的sql语句
	 */
	public String rebuildTreePath(Class<?> clazz) {

		Table table = clazz.getAnnotation(Table.class);
		if (table == null)
			return null;
		return "CALL sp_treenode_rebuild('" + table.name() + "')";
	}
}
